"""
Case Type   : Compatibility
Case Name   : 表含复合复合主键，导入数据不合法且指定参数replace
Create At   : 2023/06/02
Owner       : n@ningyali
Description :
        1.建表
        2.导入数据，数据格式合法
        3.导入数据，数据格式非法
        4.导入重复数据
        5.导入数据列数少于表的列数
        6.导入数据列数多于表的列数
        7.导入数据复合主键含空值
        8.导入数据复合主键含null
Expect      :
        1.创建成功
        2.成功
        3.合理报错
        4.成功
        5.合理报错
        6.合理报错
        7.合理报错
        8.合理报错
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class LoadData0016(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.common = Common()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0016'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.file_path = os.path.join('/home', self.pri_user.ssh_user)
        self.load_data = LoadData()

        text1 = '--pre_step1：创建数据文件；expect:创建成功'
        self.log.info(text1)
        self.data1 = "#6,'aaa',17,13313692580;#2,'bbb',18,12213691470;" \
                     "#3,'ccc',19,13525801560;#4,'ddd',18,12213691450;"
        self.data2 = "#pq1aaa17133136925802bbb18122136914701913525801560"
        self.data3 = "#6,'aaa',17,13313692580;#6,'aaa',17,13313692580;" \
                     "#6,'aaa',17,13313692580;#6,'aaa',17,13313692580;"
        self.data4 = "#6,18;#13525801560"
        self.data5 = "#6,'aaa',17,12,133,1369,258,0;" \
                     "#2,'bbb',15,16,17,18,12213691470" \
                     "#4,'ddd',18,12,21,36,91470;"
        self.data6 = "#,'aaa',,13313692580;#2,,18,12213691470;" \
                     "#3,'ccc',19,13525801570;#4,'ddd',18,12213691470;" \
                     "#5,'eee',19,13525801580;"
        self.data7 = "#null,'aaa',,13313692580;#\\n,,18,12213691470;" \
                     "#3,'ccc',19,13525801570;#4,'ddd',18,12213691470;"
        res = self.load_data.create_data_file(self.pri_user, self.case_lable,
                                              self.data1, self.data2,
                                              self.data3, self.data4,
                                              self.data5, self.data6,
                                              self.data7)
        self.assertTrue(res, '执行失败:' + text1)

        text2 = '--pre_step2：创建兼容B库；expect:创建成功'
        self.log.info(text2)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text2)

    def test_load(self):
        text = '----step1：建表；expect：建表成功----'
        self.log.info(text)
        cols = 'c_id int not null ,c_name varchar(50),c_age int,' \
               'c_cid char(11),primary key(c_id,c_name,c_cid)'
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name, cols)
        self.assertTrue(res, '执行失败:' + text)

        text = '----step2：导入数据，数据格式合法；expect：成功----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_1.txt')
        res1 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        rows, _ = self.common.parse_sql_query_res(res1)
        expect = [['6', 'aaa', '17', '13313692580'],
                  ['2', 'bbb', '18', '12213691470'],
                  ['3', 'ccc', '19', '13525801560'],
                  ['4', 'ddd', '18', '12213691450']]
        self.assertEqual(expect, rows, '执行失败' + text)

        text = '----step3：导入数据，数据格式非法；expect：合理报错----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_2.txt')
        res2 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        einfo1 = 'ERROR:  invalid input syntax for type integer'
        self.assertIn(einfo1, res2, '执行失败' + text)

        text = '----step4：导入重复数据；expect：成功----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_3.txt')
        res3 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        rows, _ = self.common.parse_sql_query_res(res3)
        expect = [['6', 'aaa', '17', '13313692580']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '----step5：导入数据列数少于表的列数；expect：合理报错----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_4.txt')
        res4 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        einfo2 = 'ERROR:  missing data for column'
        self.assertIn(einfo2, res4, '执行失败' + text)

        text = '----step6：导入数据列数多于表的列数；expect：合理报错----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_5.txt')
        res5 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        einfo3 = 'ERROR:  extra data after last expected column'
        self.assertIn(einfo3, res5, '执行失败' + text)

        text = '----step7：导入数据复合主键含空值；expect：合理报错----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_6.txt')
        res6 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        self.assertIn(einfo1+': ""', res6, '执行失败' + text)

        text = '----step8：导入数据复合主键含null；expect：合理报错----'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_7.txt')
        res7 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path)
        self.assertIn(einfo1+': "null"', res7, '执行失败' + text)

    def tearDown(self):
        self.log.info('----清理环境----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       os.path.join(self.file_path,
                                                    f"{self.case_lable}*"))
        self.assertTrue(res, '执行失败:清理环境')
        self.log.info(f'----{os.path.basename(__file__)} end----')
